﻿Imports System.Data.SqlClient
Imports System.IO

Public Class frmProspectsDetails

    Public SNo As Integer

    Sub FillUsersList()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select FullName From Users Order By FullName", cnn)
            Dim Reader As SqlDataReader

            Me.ChListContacts.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.ChListContacts.Items.Add(Reader.Item("FullName"))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillInsuranceCompany()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select CompanyName From InsuranceCompanies", cnn)
            Dim Reader As SqlDataReader

            Me.CombInsurComp.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombInsurComp.Items.Add(Reader.Item(0))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillInsuranceBrokers()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select CompanyName From InsuranceBrokers", cnn)
            Dim Reader As SqlDataReader

            Me.CombInsurBroker.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombInsurBroker.Items.Add(Reader.Item(0))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Sub FillProspectsLossReasons()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select Distinct Reason From ProspectsLossReasons", cnn)
            Dim Reader As SqlDataReader

            Me.CombLossReason.Items.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While Reader.Read
                Me.CombLossReason.Items.Add(Reader.Item(0))
            End While
            cnn.Close()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmProspectsDetails_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        FillUsersList()
        FillInsuranceCompany()
        FillInsuranceBrokers()
        FillProspectsLossReasons()

        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select SNo,PNo,ProsNo,ProsType,Status,IsNull(LossReason,N'') LossReason,CompanyName,InsuranceCompany," & _
                                      "InsuranceBroker,ClassofBusiness,BusinessSize" & _
                                      ",ContactPerson,PhoneNo,MobileNo,IsNull(Position,N'') Position,ExpiryDate,IsNull(AssignedTo,N'') AssignedTo," & _
                                      "IsNull(AssignedBy,N'') AssignedBy,IsNull(AssignedDate,N'') AssignedDate From Prospects Where SNo=" & SNo, cnn)
            Dim Reader As SqlDataReader
            Dim AssignDate, Status As String

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                If Reader.Item("AssignedDate").ToString = "1/1/1900 12:00:00 AM" Then
                    AssignDate = ""
                Else
                    AssignDate = CDate(Reader.Item("AssignedDate")).ToString("yyyy/MM/dd")
                End If

                Me.txtProsNo.Text = Reader.Item("ProsNo")
                Me.txtCompanyName.Text = Reader.Item("CompanyName")
                Me.DateTimePicker1.Text = CDate(Reader.Item("ExpiryDate"))
                Me.CombInsurComp.Text = Reader.Item("InsuranceCompany")
                Me.CombInsurBroker.Text = Reader.Item("InsuranceBroker")
                Me.txtClassOfBusiness.Text = Reader.Item("ClassofBusiness")
                Me.txtBusinessSize.Text = Reader.Item("BusinessSize")
                Me.txtContactPerson.Text = Reader.Item("ContactPerson")
                Me.txtPhoneNo.Text = Reader.Item("PhoneNo")
                Me.txtMobileNo.Text = Reader.Item("MobileNo")
                Me.txtPosition.Text = Reader.Item("Position")
                Me.txtAssignedTo.Text = Reader.Item("AssignedTo")
                Me.txtAssignedBy.Text = Reader.Item("AssignedBy")
                Me.txtAssignedOn.Text = AssignDate


                Status = Reader.Item("Status")
                Me.txtStatus.Text = Reader.Item("Status")
                Me.CombLossReason.SelectedItem = Reader.Item("LossReason")
            End While
            cnn.Close()

            'Check status
            If Status = "Active" Then
                Me.txtStatus.BackColor = Color.Yellow
                Me.RActive.Checked = True

            ElseIf Status = "Won" Then
                Me.txtStatus.BackColor = Color.GreenYellow
                Me.RWon.Checked = True

            ElseIf Status = "Lost" Then
                Me.txtStatus.BackColor = Color.Red
                Me.RLost.Checked = True

            ElseIf Status = "Non Desired" Then
                Me.txtStatus.BackColor = Color.Silver
                Me.RNonDesired.Checked = True
            End If

            If Me.RLost.Checked = True Then
                Me.CombLossReason.Enabled = True
            Else
                Me.CombLossReason.Enabled = False
                Me.CombLossReason.SelectedIndex = -1
            End If

            FillProspectsNotes()
            FillProspectsDocuments()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        SaveNotes()
    End Sub

    Sub SaveNotes()
        If Me.txtNotes.Text.Trim.Length = 0 Then
            MsgBox("Please enter your message")
            Me.txtNotes.Clear()
            Me.txtNotes.Focus()
            Exit Sub
        Else
            Try
                Me.Cursor = Cursors.WaitCursor
                Dim cmd As New SqlCommand("Insert Into ProspectsNotes (ProsNo,Notes,SavedUser) Values " & _
                                          "(@ProsNo,@Notes,@SavedUser)", cnn)
                cnn.Open()

                'Add values
                cmd.Parameters.AddWithValue("@ProsNo", SNo)
                cmd.Parameters.AddWithValue("@Notes", Me.txtNotes.Text.Trim)
                cmd.Parameters.AddWithValue("@SavedUser", CurrentUser)

                cmd.ExecuteNonQuery()
                cnn.Close()

                'Send notification
                Dim Subject, Body As String
                Dim Receivers As New ArrayList

                If Me.ChListContacts.CheckedItems.Count > 0 Then
                    Subject = "Oasis - IBS Notification - Sales Management System"
                    Body = "Your received a notification from: <B>" & CurrentUser & _
                           "</B>, regarding prospect # <B>" & Me.txtProsNo.Text & "</B><br/><br/>" & _
                           "Please don't reply to this email.<br/>" & _
                           "Sent from Oasis - Insurance Broker System"
                    For i As Integer = 0 To Me.ChListContacts.Items.Count - 1
                        If Me.ChListContacts.GetItemChecked(i) Then
                            Receivers.Add(GetUserEmail(Me.ChListContacts.Items(i)))
                        End If
                    Next

                    SendEmail(Subject, Body, Receivers)

                    MsgBox("Notification has been sent successfully")
                End If

                FillProspectsNotes()

                Me.txtNotes.Clear()
                Me.txtNotes.Focus()
                For i As Integer = 0 To Me.ChListContacts.Items.Count - 1
                    Me.ChListContacts.SetItemChecked(i, False)
                Next

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Sub FillProspectsNotes()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From ProspectsNotes Where ProsNo=" & SNo & "Order By SNo", cnn1)
            Dim Reader As SqlDataReader

            Me.txtProgressNotes.Clear()

            Dim ListHeader, ListNotes As New ArrayList

            cnn1.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Me.txtProgressNotes.Text += Reader.Item("SavedUser") & "     " & Format(CDate(Reader.Item("SavedDate")), "yyyy/MM/dd hh:mm:ss tt") & _
                                        Chr(13) & Reader.Item("Notes") & Chr(13) & Chr(13)

                ListHeader.Add(Reader.Item("SavedUser") & "     " & Format(CDate(Reader.Item("SavedDate")), "yyyy/MM/dd hh:mm:ss tt"))
                ListNotes.Add(Reader.Item("Notes"))
            End While
            cnn1.Close()

            For Each T As String In ListHeader
                txtProgressNotes.SelectionStart = txtProgressNotes.Find(T)
                Dim bfont As New Font(txtProgressNotes.Font, FontStyle.Bold)
                txtProgressNotes.SelectionFont = bfont
                txtProgressNotes.SelectionColor = Color.Blue
            Next

            Me.txtProgressNotes.ScrollToCaret()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn1.State = ConnectionState.Open Then
                cnn1.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub txtNotes_KeyUp(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles txtNotes.KeyUp
        If e.Shift And e.KeyCode = Keys.Enter Then
            Exit Sub
        ElseIf e.KeyCode = Keys.Enter Then
            SaveNotes()
        End If
    End Sub

    Sub FillProspectsDocuments()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Dir As New IO.DirectoryInfo(FTPAddress + "Prospects\" + Me.txtProsNo.Text)
            Dim DirFiles As IO.FileInfo() = Dir.GetFiles()

            Me.GridFiles.Rows.Clear()

            'list the names of all files in the specified directory
            For Each Fi As FileInfo In DirFiles
                Me.GridFiles.Rows.Add(New String() {Fi.Name, Fi.CreationTime.ToString("yyyy/MM/dd hh:mm tt"), "Open", "Delete"})
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub GridFiles_CellClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridFiles.CellClick
        If e.ColumnIndex = 2 Then
            'Dim Fi As New FileInfo(FTPAddress + "Claims\" + ClaimNo + "\" + Me.GridFiles.Rows(e.RowIndex).Cells(0).Value)
            'Fi.Open(FileMode.Open, FileAccess.ReadWrite)
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim FilePath As String = FTPAddress + "Prospects\" + Me.txtProsNo.Text.Trim + "\" + Me.GridFiles.Rows(e.RowIndex).Cells(0).Value
                System.Diagnostics.Process.Start(FilePath)

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                MsgBox(ex.ToString)
            End Try

        ElseIf e.ColumnIndex = 3 Then
            If MsgBox("Confirm delete?", MsgBoxStyle.YesNo) = MsgBoxResult.Yes Then
                Try
                    Me.Cursor = Cursors.Default

                    Dim Fi As New FileInfo(FTPAddress + "Prospects\" + Me.txtProsNo.Text.Trim + "\" + Me.GridFiles.Rows(e.RowIndex).Cells(0).Value)

                    Fi.Delete()

                    FillProspectsDocuments()

                    Me.Cursor = Cursors.Default
                Catch ex As Exception
                    Me.Cursor = Cursors.Default
                    If cnn.State = ConnectionState.Open Then
                        cnn.Close()
                    End If
                    MsgBox(ex.ToString)
                End Try
            End If
        End If
    End Sub

    Private Sub RActive_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RActive.CheckedChanged
        UpdateStatus()
    End Sub

    Sub UpdateStatus()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand
            Dim Trans As SqlTransaction

            cnn.Open()
            Trans = cnn.BeginTransaction
            cmd.Connection = cnn
            cmd.Transaction = Trans

            cmd.CommandText = "Update Prospects Set Status=@Status Where SNo=" & SNo

            'Add values

            If RActive.Checked = True Then
                cmd.Parameters.AddWithValue("@Status", "Active")

            ElseIf RWon.Checked = True Then
                cmd.Parameters.AddWithValue("@Status", "Won")

            ElseIf RLost.Checked = True Then
                cmd.Parameters.AddWithValue("@Status", "Lost")

            ElseIf RNonDesired.Checked = True Then
                cmd.Parameters.AddWithValue("@Status", "Non Desired")
            End If

            cmd.ExecuteNonQuery()

            Trans.Commit()
            cnn.Close()

            If RActive.Checked = True Then
                Me.txtStatus.BackColor = Color.Yellow
                Me.txtStatus.Text = "Active"

            ElseIf RWon.Checked = True Then
                Me.txtStatus.BackColor = Color.GreenYellow
                Me.txtStatus.Text = "Won"

            ElseIf RLost.Checked = True Then
                Me.txtStatus.BackColor = Color.Red
                Me.txtStatus.Text = "Lost"

            ElseIf RNonDesired.Checked = True Then
                Me.txtStatus.BackColor = Color.Silver
                Me.txtStatus.Text = "Non Desired"
            End If

            If Me.RLost.Checked = True Then
                Me.CombLossReason.Enabled = True

            ElseIf Me.RLost.Checked = False Then
                Me.CombLossReason.Enabled = False
                Me.CombLossReason.SelectedIndex = -1
            End If

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RNotActive_CheckedChanged(sender As System.Object, e As System.EventArgs)
        UpdateStatus()
    End Sub

    Private Sub RWon_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RWon.CheckedChanged
        UpdateStatus()
    End Sub

    Private Sub RLost_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RLost.CheckedChanged
        UpdateStatus()
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs)
        SaveNotes()
    End Sub

    Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Try
            Me.OpenFileDialog1.Filter = "All files (*.*)|*.*"
            If Me.OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

                'Set files
                Dim SelFile As FileInfo
                Dim SelFilePath, DestFilePath As String

                SelFilePath = Me.OpenFileDialog1.FileName
                SelFile = New FileInfo(SelFilePath)
                DestFilePath = FTPAddress + "Prospects\" + Me.txtProsNo.Text + "\" + SelFile.Name

                My.Computer.Network.UploadFile(SelFilePath, DestFilePath, FTPUserName, FTPPassword, True, 500)

                FillProspectsDocuments()
            End If
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button3_Click_1(sender As System.Object, e As System.EventArgs) Handles Button3.Click
        Me.ErrProv.Clear()
        If Me.RLost.Checked = True And Me.CombLossReason.SelectedIndex = -1 Then
            Me.ErrProv.SetError(Me.CombLossReason, "Please select loss reason")
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand()
                Dim Trans As SqlTransaction

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                cmd.CommandText = "Update Prospects Set LossReason=@LossReason,CompanyName=@CompanyName,InsuranceCompany=@InsuranceCompany,InsuranceBroker=@InsuranceBroker," & _
                                  "ClassofBusiness=@ClassofBusiness,BusinessSize=@BusinessSize,ContactPerson=@ContactPerson,PhoneNo=@PhoneNo," & _
                                  "MobileNo=@MobileNo,ExpiryDate=@ExpiryDate,Position=@Position Where SNo=" & SNo

                'Add values
                cmd.Parameters.AddWithValue("@LossReason", Me.CombLossReason.Text)
                cmd.Parameters.AddWithValue("@CompanyName", Me.txtCompanyName.Text.Trim)
                cmd.Parameters.AddWithValue("@InsuranceCompany", Me.CombInsurComp.Text.Trim)
                cmd.Parameters.AddWithValue("@InsuranceBroker", Me.CombInsurBroker.Text.Trim)
                cmd.Parameters.AddWithValue("@ClassofBusiness", Me.txtClassOfBusiness.Text.Trim)
                cmd.Parameters.AddWithValue("@BusinessSize", Me.txtBusinessSize.Text.Trim)
                cmd.Parameters.AddWithValue("@ContactPerson", Me.txtContactPerson.Text.Trim)
                cmd.Parameters.AddWithValue("@PhoneNo", Me.txtPhoneNo.Text.Trim)
                cmd.Parameters.AddWithValue("@MobileNo", Me.txtMobileNo.Text.Trim)
                cmd.Parameters.AddWithValue("@Position", Me.txtPosition.Text.Trim)
                cmd.Parameters.AddWithValue("@ExpiryDate", Me.DateTimePicker1.Value.ToShortDateString & " 10:10:10 AM")
                cmd.ExecuteNonQuery()

                Trans.Commit()
                cnn.Close()

                MsgBox("Updated Successfully")

                Me.Cursor = Cursors.Default
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub

    Private Sub ButtonClose_Click(sender As System.Object, e As System.EventArgs) Handles ButtonClose.Click
        Me.Close()
    End Sub

    Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles Button4.Click
        SaveNotes()
    End Sub

    Private Sub RNonDesired_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RNonDesired.CheckedChanged
        UpdateStatus()
    End Sub

    Private Sub Button1_Click_1(sender As System.Object, e As System.EventArgs)
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand()
            Dim Trans As SqlTransaction

            cnn.Open()
            Trans = cnn.BeginTransaction
            cmd.Connection = cnn
            cmd.Transaction = Trans

            cmd.CommandText = "Update Prospects Set LossReason=@LossReason Where SNo=" & SNo

            'Add values
            cmd.Parameters.AddWithValue("@CompanyName", Me.txtCompanyName.Text.Trim)
            cmd.Parameters.AddWithValue("@InsuranceCompany", Me.CombInsurComp.Text.Trim)
            cmd.Parameters.AddWithValue("@InsuranceBroker", Me.CombInsurBroker.Text.Trim)
            cmd.Parameters.AddWithValue("@ClassofBusiness", Me.txtClassOfBusiness.Text.Trim)
            cmd.Parameters.AddWithValue("@BusinessSize", Me.txtBusinessSize.Text.Trim)
            cmd.Parameters.AddWithValue("@ContactPerson", Me.txtContactPerson.Text.Trim)
            cmd.Parameters.AddWithValue("@PhoneNo", Me.txtPhoneNo.Text.Trim)
            cmd.Parameters.AddWithValue("@MobileNo", Me.txtMobileNo.Text.Trim)
            cmd.Parameters.AddWithValue("@Position", Me.txtPosition.Text.Trim)
            cmd.Parameters.AddWithValue("@ExpiryDate", Me.DateTimePicker1.Value.ToShortDateString & " 10:10:10 AM")
            cmd.ExecuteNonQuery()

            Trans.Commit()
            cnn.Close()

            MsgBox("Updated Successfully")

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub
End Class